﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace PizzaWic.customer
{
    /// <summary>
    /// permet l'acces à la bdd pour l'admin
    /// cette classe ne gère pas les erreurs en cas de mauvais paramètres (type, nombre, valeur)
    /// </summary>
    public class SqlAccess
    {
        private String connect = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=PizzaWic;Integrated Security=SSPI;Trusted_Connection=True;";

        private String[] addParamCommand = {"date","price","id_customer","id_adress"};

        private String[] addParamCP = {"id_command","id_product" };

        private String getParamCustomer = "id_customer";

        private String[] updateParamCustomer = {"id_customer", "name", "surname", "login", "password", "phone_number", "mail", "number", "street", "city", "area_code" };

        private String getParamIdProduct = "id_product";


        /// <summary>
        /// renvoi tous les products
        /// </summary>
        /// <returns></returns>
        protected internal DataSet getAllProducts()
        {
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_read", null);
            sqlConnection.Close();
            return dataSet;
        }


        /// <summary>
        /// renvoi les informations d'un product
        /// </summary>
        /// <returns></returns>
        protected internal DataSet getProductById(Object[] parameters)
        {
            Object[,] param = new Object[2, 1];

            param[1, 0] = parameters[0];
            param[0, 0] = "id_product";

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_read_one", param);
            sqlConnection.Close();
            return dataSet;
        }

        /// <summary>
        /// renvoi la liste des raw material pour un produit donné
        /// </summary>
        /// <param name="parameters">liste des valeurs des paramètres (id_product)</param>
        /// <returns></returns>
        protected internal DataSet getRawMaterialByProduct(Object[] parameters)
        {
            Object[,] param = new Object[2, 1];

            param[1, 0] = parameters[0];
            param[0, 0] = "id_product";

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "raw_material_by_product", param);
            sqlConnection.Close();
            return dataSet;
        }
        
        protected internal void addCommand(Object[] parameters)
        {
            Object[,] param = new Object[2, addParamCommand.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamCommand[i];
            }

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "command_insert", param);
            sqlConnection.Close();
            
        }

        protected internal int getLastIdCommand()
        {
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "command_get_last_id", null);
            sqlConnection.Close();
            return int.Parse(dataSet.Tables[0].Rows[0][0].ToString());
        }

        protected internal bool addCommandProduct(Object[] parameters)
        {
            Object[,] param = new Object[2, addParamCP.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamCP[i];
            }
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "command_product_insert", param);
            sqlConnection.Close();
            return true;
        }

        
        /// <summary>
        /// permet de se connecter à la base
        /// </summary>
        /// <returns>un objet SqlConnection pour se deconnecter à la fin de la transaction</returns>
        private SqlConnection dbConnect()
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = connect;
            sqlConnection.Open();
            return sqlConnection;
        }

        
        /// <summary>
        /// permet d'exuter une procédure stockée
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="ps">le nom de la procédure stockée</param>
        /// <param name="parameters">les paramètres de la procédure stockée</param>
        /// <returns>retourne éventuellement le résultat de la requète</returns>
        private DataSet psExecute(SqlConnection sqlConnection, String ps, Object[,] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = ps;

            if (parameters != null)
                for (int i = 0; i < parameters.GetLength(1); i++)
                {
                    SqlParameter sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = (String)parameters[0, i];
                    sqlParameter.Value = parameters[1, i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }

            DataSet dataSet = new DataSet();
            SqlDataAdapter sqlDataAdaptater = new SqlDataAdapter();
            sqlDataAdaptater.SelectCommand = sqlCommand;
            sqlDataAdaptater.Fill(dataSet);

            return dataSet;
        }


        internal DataSet getProductByCommand(object[] parameters)
        {
            Object[,] param = new Object[2, 1];

            param[1, 0] = parameters[0];
            param[0, 0] = "id_command";

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "command_product_read", param);
            sqlConnection.Close();
            return dataSet;
        }

        internal DataSet getCommandByCustomer(object[] parameters)
        {
            Object[,] param = new Object[2, 1];

                param[1, 0] = parameters[0];
                param[0, 0] = "id_customer";
            
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "command_user_read", param);
            sqlConnection.Close();
            return dataSet;
        }

        internal void updateCustomer(object[] parameters)
        {
            Object[,] param = new Object[2, updateParamCustomer.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = updateParamCustomer[i];
            }
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "customer_update", param);
            sqlConnection.Close();
        }



        protected internal DataSet getOneCustomer(int id_customer)
        {
            Object[,] param = new Object[2, 1];
            param[1, 0] = id_customer;
            param[0, 0] = getParamCustomer;
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "customer_read_one", param);
            sqlConnection.Close();
            return dataSet;
        }

        internal void updateQuantity(int id_product)
        {
            Object[,] param = new Object[2, 1];
            param[1, 0] = id_product;
            param[0, 0] = getParamIdProduct;
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_quantity_update", param);
            sqlConnection.Close();
            
        }



        internal object getAdressByCustomer(int p)
        {
            Object[,] param = new Object[2, 1];
           
                param[1, 0] = p;
                param[0, 0] = "id_customer";
            
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "adress_read", param);
            sqlConnection.Close();
            return dataSet;
        }
    }

}